<?php
    include_once '../../../../controlador/php/conexion/Conexion10.php';
    $con = new Conexion10();

    $listado = "Select GENPACIEN.PACNUMDOC As 'DOCUMENTO', RTrim(GENPACIEN.PACPRINOM) + ' ' +
       RTrim(GENPACIEN.PACSEGNOM) + ' ' + RTrim(GENPACIEN.PACPRIAPE) + ' ' +
       RTrim(GENPACIEN.PACSEGAPE) As 'PACIENTE', HPNDEFCAM.HCACODIGO As 'CAMA',
       HCNFOLIO.HCFECFOL As 'FECHA_SOLICITUD', HCNFOLIO.HCNUMFOL As 'NUM_FOLIO',HCNFOLIO.OID,
       (Cast(DateDiff(DD, GENPACIEN.GPAFECNAC, GetDate()) / 365.25 As INT))
       As 'EDAD', GENDETCON.GDENOMBRE As 'ENTIDAD', (Select Top (1)GENPACIENT.PACTELEFONO From GENPACIENT
       Where GENPACIENT.GENPACIEN = GENPACIEN.OID) As 'TELEFONO', GENMEDICO.GMENOMCOM
       As 'MEDICO', GENARESER.GASNOMBRE As 'AREA_DE_SERVICIO_HC', convert(nvarchar(50),HCM000008.HCCAMPO02)
       As 'CAMA_HC', GEEDESCRI AS 'ESPECIALIDAD',
       A.SIPCODIGO As 'CODIGO_SERVICIO_1', A.SIPNOMBRE As
       'PROCEDIMIENTO_1', A.SIPCODCUP As 'CUP_COD_1', A.SIPDESCUP As
       'CUP_PROCEDIMIENTO_1', B.SIPCODIGO As 'CODIGO_SERVICIO_2', B.SIPNOMBRE As
       'PROCEDIMIENTO_2', B.SIPCODCUP As ['CUP_COD_2'], B.SIPDESCUP As
       ['CUP_PROCEDIMIENTO_2'], C.SIPCODIGO As 'CODIGO_SERVICIO_3', C.SIPNOMBRE As
       'PROCEDIMIENTO_3', C.SIPCODCUP As ['CUP_COD_3'], C.SIPDESCUP As
       ['CUP_PROCEDIMIENTO_3'], D.SIPCODIGO As 'CODIGO_SERVICIO_4', D.SIPNOMBRE As
       'PROCEDIMIENTO_4', D.SIPCODCUP As 'CUP_COD_4', D.SIPDESCUP As
       'CUP_PROCEDIMIENTO_4', E.SIPCODIGO As 'CODIGO_SERVICIO_5', E.SIPNOMBRE As
       'PROCEDIMIENTO_5', E.SIPCODCUP As 'CUP_COD_5', E.SIPDESCUP As
       'CUP_PROCEDIMIENTO_5', F.SIPCODIGO As 'CODIGO_SERVICIO_6', F.SIPNOMBRE As
       'PROCEDIMIENTO_6', F.SIPCODCUP As 'CUP_COD_6', F.SIPDESCUP As
       'CUP_PROCEDIMIENTO_6', convert(nvarchar(50),HCM000008.HCCAMPO06) As 'ANESTESIOLOGO',
       convert(nvarchar(50),HCM000008.HCCAMPO07) As 'DURACION DEL PROCEDIMIENTO', convert(nvarchar(50),HCM000008.HCCAMPO08) As
       'TIPO ANESTESIA', convert(nvarchar(50),HCM000008.HCCAMPO09) As 'AYUDANTE', convert(nvarchar(50),HCM000008.HCCAMPO10) As
       'TIPO QX', convert(nvarchar(50),HCM000008.HCCAMPO11) As 'RESERVA DE SANGRE', convert(nvarchar(50),HCM000008.HCCAMPO13) As
       'INSUMO1', convert(nvarchar(50),HCM000008.HCCAMPO12) As 'CARACTERISTICA1', convert(nvarchar(50),HCM000008.HCCAMPO14) As
       'INSUMO2', convert(nvarchar(50),HCM000008.HCCAMPO15) As 'CARACTERISTICA2', convert(nvarchar(50),HCM000008.HCCAMPO16) As
       'INSUMO3', convert(nvarchar(50),HCM000008.HCCAMPO17) As 'CARACTERISTICA3', convert(nvarchar(50),HCM000008.HCCAMPO18) As
       'AYUNO', convert(nvarchar(50),HCM000008.HCCAMPO19) As 'RESERVA DE CAMA UCI', convert(nvarchar(500),HCM000008.HCCAMPO20) As
       'OBSERVACION',programa.proestado as 'estado_programacion',cancelado.hcancelado,programa.proid
From HCNFOLIO Inner Join
     HCM000008 On HCM000008.HCNFOLIO = HCNFOLIO.OID Inner Join
     GENPACIEN On HCNFOLIO.GENPACIEN = GENPACIEN.OID Inner Join
     ADNINGRESO On HCNFOLIO.ADNINGRESO = ADNINGRESO.OID Inner Join
     GENMEDICO On HCNFOLIO.GENMEDICO = GENMEDICO.OID Inner Join
     GENESPMED ON GENESPMED.MEDICOS = GENMEDICO.OID Inner Join
     GENESPECI ON GENESPMED.ESPECIALIDADES = GENESPECI.OID Inner Join
     GENDETCON On ADNINGRESO.GENDETCON = GENDETCON.OID Left Outer Join
     GENARESER On HCM000008.HCCAMPO01 = GENARESER.OID Left Outer Join
     GENSERIPS As A On HCM000008.HCCAMPO03 = A.OID Left Outer Join
     GENSERIPS As B On HCM000008.HCCAMPO04 = B.OID Left Outer Join
     GENSERIPS As C On HCM000008.HCCAMPO05 = C.OID Left Outer Join
     GENSERIPS As D On HCM000008.HCCAMPO05 = D.OID Left Outer Join
     GENSERIPS As E On HCM000008.HCCAMPO05 = E.OID Left Outer Join
     GENSERIPS As F On HCM000008.HCCAMPO05 = F.OID Left Join
     HPNESTANC On HPNESTANC.ADNINGRES = ADNINGRESO.OID And HPNESTANC.ADNINGRES =
     HCNFOLIO.ADNINGRESO Left Join
     HPNDEFCAM On HPNESTANC.HPNDEFCAM = HPNDEFCAM.OID
     left join planillaUsuario..pcprograma as programa on HCNFOLIO.OID = programa.folioid
     outer apply(select top 1 hiscancelado as hcancelado
                 from planillaUsuario..pchistori
                 where pchistori.proid = programa.proid
                 order by hisfecha desc)as cancelado
Where HCNFOLIO.HCFECFOL > CONVERT(DATETIME, GETDATE() - 30, 102) AND
	 /*HCNFOLIO.HCFECFOL Between '01/12/2013 00:00:00.000' AND GETDATE() And*/
      HPNESTANC.HESFECSAL Is Null And ADNINGRESO.AINURGCON != 1 And
      HCM000008.HCCM09N21 = 'HOSPITALARIO' 
      And GEMPRINCIPAL='1'
Order By HCNFOLIO.HCFECFOL desc";
    $res = $con->query($listado);

    while($re = mssql_fetch_array($res)){
        $lista[] = array_map(utf8_encode,$re);
    }
    
    echo json_encode($lista);
    
    $con->cerrarConexion();
    
?>